package com.trex.modules.utils;

import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.IntStream;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

/**
 * Excel操作类
 * 
 * @author 2021-08-24
 */
public class ExcelUtils {
	private ExcelUtils() {
		throw new IllegalStateException("Utility class");
	}
	
	/**
	 * 
	 * @param response
	 * @param fileName
	 */
	public static void setResponseHeader(HttpServletResponse response, String fileName) {
		try {
			fileName = new String(fileName.getBytes(),StandardCharsets.ISO_8859_1);
			response.setContentType("application/octet-stream;charset=ISO8859-1");
			response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
			response.addHeader("Pargam", "no-cache");
			response.addHeader("Cache-Control", "no-cache");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	private static HSSFCellStyle createCellStyle(HSSFWorkbook wb) {
		return createCellStyle(wb, null, VerticalAlignment.CENTER, BorderStyle.THIN);
	}

	private static HSSFCellStyle createCellStyle(HSSFWorkbook wb, HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment) {
		return createCellStyle(wb, horizontalAlignment, verticalAlignment, BorderStyle.NONE);
	}

	private static HSSFCellStyle createCellStyle(HSSFWorkbook wb, HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment, BorderStyle... borderStyles) {
		HSSFCellStyle cellStyle = wb.createCellStyle();
		BorderStyle defaultBorderStyle = ArrayUtils.isEmpty(borderStyles) ? BorderStyle.THIN : borderStyles[0];
		cellStyle.setBorderTop(ArrayUtils.get(borderStyles, 0, defaultBorderStyle));
		cellStyle.setBorderRight(ArrayUtils.get(borderStyles, 1, defaultBorderStyle));
		cellStyle.setBorderBottom(ArrayUtils.get(borderStyles, 2, defaultBorderStyle));
		cellStyle.setBorderLeft(ArrayUtils.get(borderStyles, 3, defaultBorderStyle));
		if (null != horizontalAlignment) cellStyle.setAlignment(horizontalAlignment);
		cellStyle.setVerticalAlignment(verticalAlignment);
		return cellStyle;
	}

	/**
	 * 创建标题行
	 * 
	 * @param row excel行
	 * @param sheet excel页
	 * @param sheetName 标题名
	 * @param wb excel对象
	 * @param length 列数
	 */
	public static void createTitleRow(HSSFRow row, HSSFSheet sheet, String sheetName, HSSFWorkbook wb, int length) {
		// 创建字体样式
		HSSFFont font = wb.createFont();
		font.setBold(true);// 粗体显示
		font.setFontHeightInPoints((short) 12); // 字体高度
		// 创建单元格样式
		HSSFCellStyle titleStyle = createCellStyle(wb, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
		titleStyle.setFont(font);
		
		HSSFCell cell = null;
		row.setHeightInPoints(25);// 行高
		cell = row.createCell(0);
		cell.setCellValue(sheetName);
		cell.setCellStyle(titleStyle);
		CellRangeAddress region = new CellRangeAddress(0, 0, 0, length - 1);
		sheet.addMergedRegion(region);
	}
	
	private static Integer createCellTitleRow(HSSFWorkbook wb,HSSFSheet sheet, String[] title, List<MergedCellBean> mergeBeanList) {
		Integer rowColumn = 0;
		if (null == mergeBeanList || mergeBeanList.isEmpty()) {
			createColumnRow(sheet, title, wb);
			rowColumn = 2;
		} else {
			rowColumn = createColumnMergeRow(sheet, wb, mergeBeanList);
		}
		return rowColumn;
	}

	/**
	 * 创建二级表头
	 * 
	 * @param sheet
	 * @param wb
	 * @param mergeBeanList
	 * @return
	 */
	public static Integer createColumnMergeRow(HSSFSheet sheet, HSSFWorkbook wb, List<MergedCellBean> mergeBeanList) {
		int rowNum = 0;
		HSSFCell cell = null;
		HSSFCellStyle titleStyle = createCellStyle(wb, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, BorderStyle.THIN);
		// 创建字体样式
		HSSFFont font = wb.createFont();
		font.setBold(true);// 粗体显示
		font.setFontHeightInPoints((short) 12); // 字体高度
		titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());// 设置背景色
		titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		titleStyle.setWrapText(true);
		
		titleStyle.setFont(font);
		for (int i = 0; i < mergeBeanList.size(); i++) {
			MergedCellBean mbean = mergeBeanList.get(i);
			int startRow = mbean.getStartRow();
			int endRow = mbean.getEndRow();
			int startCol = mbean.getStartCol();
			int endCol = mbean.getEndCol();

			HSSFRow row = sheet.getRow(startRow);
			if(null == row) {
				row = sheet.createRow(startRow);
				row.setHeightInPoints(21);
			}
			
			if (startRow == endRow) {
				if(startCol == endCol) {
					cell = row.createCell(startCol);
				} else {
					CellRangeAddress region = new CellRangeAddress(startRow, endRow, startCol, endCol - 1);
					RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
					RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
					RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
					RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);
					sheet.addMergedRegion(region);
					cell = row.createCell(startCol);
				}
			} else if (startCol == endCol) {
				CellRangeAddress region = new CellRangeAddress(startRow, endRow, startCol, endCol);
				RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
				RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
				RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
				RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);
				sheet.addMergedRegion(region);
				cell = row.createCell(startCol);
				rowNum = endRow;
			}
			if(cell!=null) {
				cell.setCellValue(mbean.getCellValue());
				cell.setCellStyle(titleStyle); 
			}
		}
		return rowNum + 1;
	}

	/**
	 * 创建列头
	 * 
	 * @param row excel行
	 * @param sheet excel页
	 * @param title 列头数组
	 * @param wb excel对象
	 */
	public static List<Integer> createColumnRow(HSSFSheet sheet, String[] title, HSSFWorkbook wb) {
		ArrayList<Integer> list = new ArrayList<>();
		HSSFCell cell = null;
		HSSFCellStyle titleStyle = createCellStyle(wb, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, BorderStyle.THIN);
		// 创建字体样式
		HSSFFont font = wb.createFont();
		font.setBold(true);// 粗体显示
		font.setFontHeightInPoints((short) 12); // 字体高度
		titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());// 设置背景色
		titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		titleStyle.setFont(font);
		titleStyle.setWrapText(true);
		HSSFRow row = sheet.createRow(1);
		row.setHeightInPoints(21);// 行高
		for (int i = 0; i < title.length; i++) {
			cell = row.createCell(i);
			cell.setCellValue(title[i].replaceAll("＜br[^＞]*＞", "\r\n").replaceAll("＜[^＞]*＞", ""));
			cell.setCellStyle(titleStyle);
			int length = title[i].getBytes().length * 256 + 256;// 表头列宽
			sheet.setColumnWidth(i, length);
			list.add(length);
		}
		return list;
	}

	/**
	 * 获取单元格值，并对类型处理
	 * 
	 * @param r
	 * @param c
	 * @param row
	 * @return
	 */
	public static String getCellValue(int r, int c, Row row) {
		Cell cell = row.getCell(c);
		String value = null;
		if (cell == null) {
			cell = row.createCell(0);
			cell.setCellValue("");
		}
		try {
			if (cell.getCellType() == CellType.STRING) {
				value = cell.getStringCellValue().trim();
			} else if (cell.getCellType() == CellType.BOOLEAN) {
				value = String.valueOf(cell.getBooleanCellValue());
			} else if (cell.getCellType() == CellType.NUMERIC) {
				// 处理日期格式的Excel数据
				if (DateUtil.isCellDateFormatted(cell)) {
					Date date = cell.getDateCellValue();
					value = DateFormatUtils.format(date, "yyyy-MM-dd");
				} else {
					// 处理科学计数法
					Double temp = cell.getNumericCellValue() ;
					String strTemp = temp.toString();
					
					if(strTemp.contains("E")) {
						DecimalFormat df = new DecimalFormat("0");
						value = df.format(temp);
					} else {
						value = strTemp;
					}
				}
			} else if (cell.getCellType() == CellType.FORMULA) {
				// 处理数字和公式
				value = String.valueOf(Double.valueOf(cell.getNumericCellValue()));
				value = value.replaceAll("0+$", "");
				value = value.replaceAll("[.]$", "");
			} else {
				value = "";
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new IllegalStateException("导入失败!第" + (r + 1) + "行第" + (c + 1) + "列,格式有误\n");
		}
		return value;
	}
	
	public static String getCellValue(int r, int c, Row row, String isBlankMessage) {
		String value = getCellValue(r,c,row);
		if (StringUtils.isBlank(value.trim()) && StringUtils.isNotBlank(isBlankMessage)) throw new UnsupportedOperationException(isBlankMessage);
		return value;
	}

	/**
	 * 通过对象反射获取值
	 * 
	 * @param src
	 * @param attrNames
	 * @return
	 * @throws Exception
	 */
	public static Object getValue(Object src, String attrNames) {
		if (null == src)
			return null;
		Class<?> clazz = src.getClass();
		String fieldName = attrNames;
		int index = attrNames.indexOf(".");
		if (-1 != index) {
			fieldName = attrNames.substring(0, index);
		}
		char[] chars = fieldName.toCharArray();

		try {
			Method method = clazz.getMethod("get" + ((char) (chars[0] - 32)) + fieldName.substring(1));
			if (method != null) {
				if (-1 == index)
					return method.invoke(src);
				else
					return getValue(method.invoke(src), attrNames.substring(index + 1));
			}
		} catch (Exception e) {
			return "";
		}
		return null;
	}

	/**
	 * 获取map中的属性值
	 * 
	 * @param json
	 * @param attrNames
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	private static Object getValue(Map<String, Object> map, String attrNames) {
		String fieldName = attrNames;
		int index = attrNames.indexOf(".");
		if (-1 != index) {
			fieldName = attrNames.substring(0, index);
		}
		Object obj = map.get(fieldName);
		if (obj != null) {
			if (-1 == index)
				return obj;
			else
				return getValue((Map<String, Object>)map.get(fieldName), attrNames.substring(index + 1));
		}
		return null;
	}
	
	private static void setCellWidth(HSSFSheet sheet, String[] columnWidth) {
		IntStream.range(0, columnWidth.length).forEach(i -> sheet.setColumnWidth(i, Integer.valueOf(columnWidth[i]) * 35));
	}

	/**
	 * 导出数据
	 * 
	 * @param sheetName
	 * @param title 列头名数组w
	 * @param attrName 属性名数组
	 * @param list 数据集合
	 * @param obj 类型对象
	 * @param mergeBeanList 合并列头组合集合
	 * @param columnWidth 列宽
	 * @return
	 */
	public static <T> HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[] attrName, List<T> list,List<MergedCellBean> mergeBeanList, String[] columnWidth) {
		return getHSSFWorkbook(sheetName, title, attrName, list, mergeBeanList, columnWidth, null);
	}
	
	@SuppressWarnings("unchecked")
	public static <T> HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[] attrName, List<T> list, List<MergedCellBean> mergeBeanList, String[] columnWidth, Map<String, Function<T, Object>> converters) {
		// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
		HSSFWorkbook wb = new HSSFWorkbook();
		// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet(sheetName);
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
		HSSFRow rowTitle = sheet.createRow(0);
		// 创建标题
		createTitleRow(rowTitle, sheet, sheetName, wb, title.length);
		// 创建列头
		Integer rowColumn = createCellTitleRow(wb, sheet, title, mergeBeanList);

		HSSFCellStyle cellStyleLeft = createCellStyle(wb,HorizontalAlignment.LEFT, VerticalAlignment.CENTER, BorderStyle.THIN);
		HSSFCellStyle cellStyleRight = createCellStyle(wb,HorizontalAlignment.RIGHT, VerticalAlignment.CENTER, BorderStyle.THIN);
		try {
			// 创建内容
			IntStream.range(0, list.size()).forEach(i -> {
				HSSFRow row = sheet.createRow(i + rowColumn);
				row.setHeightInPoints(18);// 目的是想把行高设置成20px
				Object object = list.get(i);
				IntStream.range(0, attrName.length).forEach(a -> {
					String fieldName = attrName[a];
					HSSFCell cell = row.createCell(a);
					HSSFCellStyle cellStyle = null;
					Object val = null;

					if (null != converters && converters.containsKey(fieldName)) val = converters.get(fieldName).apply((T)object);
					else val = getValue(object, fieldName);

					if (null == val) {
						cell.setCellValue("");
						cellStyle = cellStyleLeft;
					} else if (val instanceof Double || val instanceof Integer || val instanceof Long) {
						NumberFormat nf = NumberFormat.getInstance();
						// 设置保留多少位小数
						nf.setMaximumFractionDigits(6);
						// 取消科学计数法
						nf.setGroupingUsed(false);
						cell.setCellValue(nf.format(val));
						cellStyle = cellStyleRight;
					} else {
						cell.setCellValue(val.toString());
						cellStyle = cellStyleLeft;
					}
					cell.setCellStyle(cellStyle);
				});
			});
			setCellWidth(sheet, columnWidth);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return wb;
	}

	/**
	 * 导出map集合数据
	 * 
	 * @param sheetName
	 * @param title 列头名数组
	 * @param attrName 属性名数组
	 * @param list 数据集合
	 * @param mergeBeanList 合并列头组合集合
	 * @param columnWidth 列宽
	 * @return
	 */
	public static  HSSFWorkbook getHSSFWorkbookMap(String sheetName, String[] title, String[] attrName, List<Map<String, Object>> list, List<MergedCellBean> mergeBeanList, String[] columnWidth) {
		return getHSSFWorkbookMap(sheetName, title,  attrName,  list,  mergeBeanList,  columnWidth, null);
	}

	public static  HSSFWorkbook getHSSFWorkbookMap(String sheetName, String[] title, String[] attrName, List<Map<String, Object>> list, List<MergedCellBean> mergeBeanList, String[] columnWidth, Map<String, Function<Map<String, Object>, Object>> converters) {
		// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
		HSSFWorkbook wb = new HSSFWorkbook();
		// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet(sheetName);
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
		HSSFRow rowTitle = sheet.createRow(0);
		// 第四步，创建单元格，并设置单元格样式
		HSSFCellStyle cellStyle = createCellStyle(wb);
		// 创建标题
		createTitleRow(rowTitle, sheet, sheetName, wb, title.length);
		// 创建列头
		Integer rowColumn = createCellTitleRow(wb, sheet, title, mergeBeanList);
		try {
			IntStream.range(0, list.size()).forEach(i -> {
				HSSFRow row = sheet.createRow(i + rowColumn);
				row.setHeightInPoints(18); // 目的是想把行高设置成20px
				Map<String, Object> map = list.get(i);
				IntStream.range(0, attrName.length).forEach(a->{
					String fieldName = attrName[a];
					HSSFCell cell = row.createCell(a);
					Object val = null;

					if (null != converters && converters.containsKey(fieldName)) val = converters.get(fieldName).apply(map);
					else val = getValue(map, fieldName);

					if (null == val) {
						cell.setCellValue("");
					} else if (val instanceof Double || val instanceof Integer || val instanceof Long || val instanceof BigDecimal) {
						cell.setCellValue(Double.parseDouble(val.toString()));
					} else {
						cell.setCellValue(val.toString());
					}
					cell.setCellStyle(cellStyle);
				});
			});
			setCellWidth(sheet, columnWidth);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return wb;
	}

}
